﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
   public class DbManager : IDisposable
    {
        #region
        private string _ConnectionString;
        /// <summary>
        /// 有效连接字符串
        /// </summary>
        public string ConnectionString
        {
            get
            {
                if (_ConnectionString == null)
                {
                    return System.Configuration.ConfigurationManager.ConnectionStrings["DataContext"].ToString();
                }
                else
                {
                    return _ConnectionString;
                }
            }
            set { _ConnectionString = value; }
        }
        public void Dispose()
        {
            GC.Collect();
        }

        #endregion


        #region IDbManager 成员
        /// <summary>
        /// 运行没有结果sql语句
        /// </summary>
        /// <param name="sql">一句有效的sql语句</param>
        public void RunNoneResultSql(string sql)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandTimeout = 300;
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
        /// <summary>
        /// 返回datatable类型的sql语句
        /// </summary>
        /// <param name="sql">有效的sql语句</param>
        /// <returns>datatable</returns>
        public DataTable RunDataTableResultSql(string sql)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                conn.Close();
                return dt;
            }
        }
        /// <summary>
        /// 返回第一条第一个数量
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object GetFirstData(string sql)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandTimeout = 300;
                object obj = cmd.ExecuteScalar();
                conn.Close();
                return obj;
            }
        }
        /// <summary>
        /// 返回dataset类型的sql语句
        /// </summary>
        /// <param name="sql">有效的sql语句</param>
        /// <returns>dataset类型</returns>
        public DataSet RunDataSetResultSql(string sql)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                conn.Close();
                return ds;
            }
        }
        /// <summary>
        /// 返回datareader类型的sql语句
        /// </summary>
        /// <param name="sql">有效的sql语句</param>
        /// <returns>datareader类型</returns>
        public SqlDataReader RunDataReaderResultSql(string sql)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandTimeout = 300;
            SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return sdr;
        }
        /// <summary>
        /// 返回datatable类型的带参数列表的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>datatable类型</returns>
        public DataTable RunDataTableResultScoreProcWithParameters(string procName, SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(procName, conn);
                cmd.CommandTimeout = 300;
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter param in parameters)
                {
                    cmd.Parameters.Add(param);
                }

                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                cmd.Parameters.Clear();
                conn.Close();
                return dt;
            }
        }
        /// <summary>
        /// 返回datatable类型的没有参数与的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>datatable类型</returns>
        public DataTable RunDataTableResultScoreProcWithNoneParameters(string procName)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(procName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 300;
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                conn.Close();
                return dt;
            }
        }
        /// <summary>
        /// 运行没有结果带参数列表的sql语句
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">参数列表</param>
        public void RunNoneResultScoreProcWithParameters(string procName, SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(procName, conn);
                cmd.CommandTimeout = 300;
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter param in parameters)
                {
                    cmd.Parameters.Add(param);
                }
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                conn.Close();
            }
        }
        /// <summary>
        /// 运行没有结果的没有参数的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        public void RunNoneResultScoreProcWithNoneParameters(string procName)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(procName, conn);
                cmd.CommandTimeout = 300;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
        /// <summary>
        /// 返回datareader类型的带参数列表的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>datareader类型</returns>
        public SqlDataReader RunDataReaderResultScoreProcWithParameters(string procName, SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(procName, conn);
            cmd.CommandTimeout = 300;
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter param in parameters)
            {
                cmd.Parameters.Add(param);
            }
            SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return sdr;
        }
        /// <summary>
        /// 返回datareader类型的没有参数的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>datareader类型</returns>
        public SqlDataReader RunDataReaderResultScoreProcWithNoneParameters(string procName)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(procName, conn);
            cmd.CommandTimeout = 300;
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return sdr;
        }
        /// <summary>
        /// 返回一条记录带参数列表的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>datareader类型</returns>
        public string RunOneResultScoreProcWithParameters(string procName, SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(procName, conn);
            cmd.CommandTimeout = 300;
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter param in parameters)
            {
                cmd.Parameters.Add(param);
            }
            string str_return = "";
            object o = cmd.ExecuteScalar();
            if (o == null)
            {
                str_return = o.ToString();
            }
            cmd.Parameters.Clear();
            return str_return;
        }

        /// <summary>
        /// 根据参数名称、参数类型(Int/nvarchar等等)、参数数据最大大小、参数类型(input/output)、参数值来实例化一个存储过程参数
        /// </summary>
        /// <param name="paraName">参数名称</param>
        /// <param name="dbType">参数类型(Int/nvarchar等等)</param>
        /// <param name="paraSize">参数数据最大大小(Int类型)</param>
        /// <param name="direction">参数类型(Input/Output)</param>
        /// <param name="value">参数值</param>
        /// <returns>存储过程参数类型</returns>
        public SqlParameter GetParameter(string paraName, SqlDbType dbType, int paraSize, ParameterDirection direction, object value)
        {
            SqlParameter param = new SqlParameter();
            param.ParameterName = paraName;
            param.SqlDbType = dbType;
            param.Size = paraSize;
            param.Direction = direction;
            param.Value = value;
            return param;
        }
        /// <summary>
        /// 根据参数名称、参数类型(Int/nvarchar等等)、参数数据最大大小、参数值来实例化一个Input类型的存储过程参数
        /// </summary>
        /// <param name="paraName">参数名称</param>
        /// <param name="dbType">参数类型(Int/nvarchar等等)</param>
        /// <param name="paraSize">参数数据最大大小</param>
        /// <param name="value">参数值</param>
        /// <returns>存储过程参数类型</returns>
        public SqlParameter GetParameter(string paraName, SqlDbType dbType, int paraSize, object value)
        {
            SqlParameter param = new SqlParameter();
            param.ParameterName = paraName;
            param.SqlDbType = dbType;
            param.Size = paraSize;
            param.Direction = ParameterDirection.Input;
            param.Value = value;
            return param;
        }
        /// <summary>
        /// 根据参数名称、参数类型(Int/nvarchar等等)、参数数据最大大小来实例化一个Output类型的存储过程参数
        /// </summary>
        /// <param name="paraName">参数名称</param>
        /// <param name="dbType">参数类型(Int/nvarchar等等)</param>
        /// <param name="paraSize">参数数据最大大小</param>
        /// <returns>存储过程参数类型</returns>
        public SqlParameter GetParameter(string paraName, SqlDbType dbType, int paraSize)
        {
            SqlParameter param = new SqlParameter();
            param.ParameterName = paraName;
            param.SqlDbType = dbType;
            param.Size = paraSize;
            param.Direction = ParameterDirection.Output;
            return param;
        }

        #endregion
    }
}
